package visiopuce.DAO;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;

import visiopuce.HibernateUtil;
import visiopuce.objets.Personne;
import visiopuce.objets.Prelevement;
import visiopuce.objets.Secteur;

public class PrelevementDAOImpl extends GenericDAOImpl<Prelevement, BigDecimal> implements PrelevementDAO {

	/** Constructeur priv� */
	private PrelevementDAOImpl() {
	}

	/** Instance unique non pr�initialis�e */
	private static PrelevementDAOImpl INSTANCE = null;

	/** Point d"acc�s pour l"instance unique du singleton */
	public static PrelevementDAO getInstance() {
		if (INSTANCE == null) {
			INSTANCE = new PrelevementDAOImpl();
		}
		return INSTANCE;
	}

	public List<Prelevement> findAllByidPersonne(Integer idPersonne) {
		List<Prelevement> prelevements = null;
		String sql = "SELECT p FROM Prelevement p WHERE p.personne.idPersonne = :idPersonne";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPersonne", idPersonne);
		prelevements = findMany(query);
		return prelevements;
	}

	public String[] getNumeroFromNumero(String numero) {
		String[] numeros = null;
		List<String> noms = null;
		String sql = "SELECT distinct p.numeroPrelevement FROM Prelevement p WHERE p.numeroPrelevement LIKE :numero";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("numero", numero + "%");
		noms = query.list();
		if (noms.size() > 0) {
			numeros = noms.toArray(new String[0]);
		}
		return numeros;
	}

	public Prelevement getPrelevementByNumero(String numero) {
		String sql = "SELECT p FROM Prelevement p WHERE p.numeroPrelevement LIKE :numero";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("numero", numero);
		return findOne(query);
	}

	@Override
	public Secteur getSecteurByIdPrelevement(Integer idPrelevement) {
		Secteur secteur = null;
		if (idPrelevement != null) {
			String sql = null;
			Query query = null;
			sql = "SELECT p.secteur FROM Prelevement p WHERE p.idPrelevement = :idPrelevement";
			query = HibernateUtil.getSession().createQuery(sql).setParameter("idPrelevement", idPrelevement);
			secteur = (Secteur) query.uniqueResult();
		}
		return secteur;
	}

	@Override
	public Prelevement getPrelevementByPersonneSecteur(Integer idPersonne, int idSecteur) {
		Prelevement p = null;
		List<Prelevement> prelevements = null;
		String sql = "SELECT p FROM Prelevement p WHERE p.personne.idPersonne = :idPersonne and p.secteur.idSecteur = :idSecteur";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPersonne", idPersonne).setParameter("idSecteur", idSecteur);
		prelevements = findMany(query);
		if (prelevements != null && prelevements.size() > 0) {
			p = prelevements.get(0);
		}
		return p;
	}

	@Override
	public List<Prelevement> getPrelevementByLettre(String lettre) {
		List<Prelevement> prels = null;
		String sql = "SELECT distinct p FROM Prelevement p WHERE p.numeroPrelevement LIKE :numero OR p.numeroPrelevement LIKE :numero2";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("numero", lettre + "13%").setParameter("numero2", lettre + "14%");
		prels = findMany(query);
		return prels;
	}

	@Override
	public List getAllUFPresc() {
		String sql = "SELECT distinct p.ufPresc FROM Prelevement p ";
		Query query = HibernateUtil.getSession().createQuery(sql);
		List ufPresc = query.list();
		return ufPresc;
	}

	@Override
	public Personne getPersonneByIEP(String nda) {
		Personne personne = null;
		String sql = "SELECT  p.personne FROM Prelevement p WHERE p.numSejour = :numSejour";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("numSejour", nda);
		List<Personne> personnes = (ArrayList<Personne>) query.list();
		if (personnes != null && personnes.size() > 0) {
			personne = personnes.get(0);
		}
		return personne;
	}

	@Override
	public List<String> getNumSejourNonTermine() {
		List<String> numSejours = null;
		String sql = "SELECT distinct p.numSejour FROM Prelevement p WHERE p.numeroPrelevement in ('A14-0305','A14-0428','A14-0532','A14-0691','A14-0614','A14-0072','A14-0164','A14-0696','A14-0694','A14-0673','A14-0531','A14-0367','A14-0552','A14-0745','A14-0822','A14-0506','A14-0507','A14-0494','A14-0513','A14-0585','A14-0039','A14-0542','A14-0454','A14-0317','A14-0090','A14-0073','A14-0742','A14-0630','A14-0790','A14-0858','A14-0859','A14-0806','A14-0807','A14-0809','A14-0810','A14-0811','A14-0812','A14-0813','A14-0816','A14-0818','A14-0819','A14-0525','A14-0788','A14-0591','A14-0592','A14-0775','A14-0767','A14-0753','A14-0795','A14-0608','A14-0732','A14-0869','A14-0604','A14-0605','A14-0836','A14-0837','A14-0452','A14-0763','A14-0776','A14-0777','A14-0706','A14-0787','A14-0797','A14-0571','A14-0572','A14-0518','A14-0884','A14-0885','A14-0879','A14-0880','A14-0886','A14-0624','A14-0835','A14-0597','A14-0738','A14-0804','A14-0805','A14-0801','A14-0337','A14-0267','A14-0667','A14-0442','A14-0590','A14-0736','A14-0781','A14-0780','A14-0782','A14-0771','A14-0764','A14-0799','A14-0796','A14-0791','A14-0758','A14-0740','A14-0760','A14-0759','A14-0756','A14-0757','A14-0737','A14-0779','A14-0487','A14-0786','A14-0754','A14-0728','A14-0493','A14-0854','A14-0761','A14-0671','A14-0675','A14-0771','A14-0784','A14-0684','A14-0687','A14-0783','A14-0786','A14-0768','A14-0762','A14-0792','A14-0789','A14-0050','A14-0066','A14-0018','A14-0846','A14-0847','A14-0083','A14-0558','A14-0559','A14-0715','A14-0308','A14-0299','A14-0839','A14-0340','A14-0887','A14-0865','A14-0739','A14-0746','A14-0747','A14-0702','A14-0709','A14-0208','A14-0252','A14-0830','A14-0729','A14-0872','A14-0659','A14-0825','A14-0826','A14-0827','A14-0828','A14-0829','A14-0833','A14-0834','A14-0838','A14-0841','A14-0842','A14-0852','A14-0855','A14-0856','A14-0857','A14-0861','A14-0862','A14-0863','A14-0866','A14-0867','A14-0868','A14-0871','A14-0873','A14-0874','A14-0875','A14-0876','A14-0877','A14-0878','A14-0881','A14-0882','A14-0886','A14-0888','A14-0889','A14-0890','A14-0891','A14-0049','A14-0892','A14-0893','A14-0894','A14-0895','A14-0235','A14-0055','A14-0534','A14-0190','A14-0545','A14-0546','A14-0385','A14-0488','A14-0577','A14-0433','A14-0849','A14-0052','A14-0680','A14-0705','P13-1161','P13-1112','P13-1113','P13-1043','P13-1173','P13-1359','P13-0474','P13-0693','P13-0056','P13-0462','P13-0558','P13-0414','P12-0340','P12-0339','P12-1243','P12-1046','F14-0076','F14-0039','F14-0040','F14-0065','F14-0072','F14-0074','LA14-0217','LA14-0257','LA14-0268','LA14-0270','LA14-0271','LA14-0272','LA14-0273','LA14-0274','LA14-0269','LA14-0264','LA14-0267','LA14-0260','VC14-0103','VC14-0104','VC14-0105','VC14-0102','VC14-0101','VC14-0091','SF14-0009','S13-0300','S10-0651','S10-0354','S10-0335','S14-0613','S14-0821','S14-0749','S14-0750','S14-0819','S14-0725','S14-0355','S14-0755','S14-0825','S14-0832','S14-0810','S14-0809','S14-0883','S14-0826','S14-0865','S14-0866','S14-0823','S14-0814','S14-0813','S14-0822','S14-0812','S14-0674','S14-0833','S14-0834','S14-0835','S14-0836','S14-0840','S14-0841','S14-0842','S14-0844','S14-0845','S14-0847','S14-0848','S14-0849','S14-0851','S14-0851','S14-0853','S14-0854','S14-0855','S14-0856','S14-0858','S14-0859','S14-0862','S14-0863','S14-0867','S14-0868','S14-0869','S14-0870','S14-0871','S14-0872','S14-0578','S14-0873','S14-0874','S14-0875','S14-0877','S14-0876','S14-0878','S14-0879','S14-0880','S14-0881','S14-0882','S14-0551','S14-0733','S14-0790','S14-0709','S14-0780','S14-0786','S14-0787','S14-0788','S14-0789','S14-0795','S14-0798','S14-0799','S14-0806','S14-0802','S14-0803','S14-0640','S14-0701','S14-0574','S14-0471','S14-0545','S14-0430','S14-0805','S14-0804','S14-0771','S14-0778','S14-0779','S14-0729','S14-0734','S14-0735','S14-0736','S14-0737','S14-0738','S14-0751','S14-0754','S14-0756','S14-0757','S14-0760','S14-0763','S14-0764','S14-0765','S13-0689','F14-0003','S14-0269','P14-0271','P14-0291','P14-0292','S14-0293','A14-0666','A14-0626','S14-0850','S14-0843','S14-0775','S14-0678','P13-0223-2')";
		Query query = HibernateUtil.getSession().createQuery(sql);
		numSejours = (List<String>) query.list();
		return numSejours;
	}

	@Override
	public List<Prelevement> getPrelevementByNumSejour(String numSejour) {
		List<Prelevement> prelevements = null;
		String sql = "SELECT p FROM Prelevement p WHERE p.numSejour = :numSejour";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("numSejour", numSejour);
		prelevements = findMany(query);
		return prelevements;
	}

	@Override
	public List<String> getNumSejourCotationAEnvoyer() {
		List<String> numSej = null;
		String sql = "SELECT distinct p.numSejour FROM Prelevement p WHERE p.cotationEnvoye = 1";
		Query query = HibernateUtil.getSession().createQuery(sql);
		numSej = (List<String>) query.list();
		return numSej;
	}
}